package com.nba.dao.impl;

import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import com.nba.bean.Player;
import com.nba.bean.inherit.PlayerInherit;
import com.nba.bean.inherit.TeamInherit;
import com.nba.dao.PlayerDao;
import com.nba.util.DBUtil;

public class PlayerDaoImpl implements PlayerDao {
	JdbcTemplate jt = new JdbcTemplate(DBUtil.getDataSource());
	
	@Override
	public List<Player> selectPlayerAll() {
		String sql = "SELECT * FROM player";
		List<Player> list = jt.query(sql, new BeanPropertyRowMapper<Player>(Player.class));
		return list;
	}

	@Override
	public List<PlayerInherit> selectPlayerAll(String keyword, int pageNum, int pageSize) {
		String sql = "SELECT * FROM ptt WHERE player_name LIKE ? OR english_name LIKE ? OR player_titl LIKE ? OR player_body LIKE ? OR team_name LIKE ? OR player_position LIKE ?  OR player_height LIKE ? OR player_weight LIKE ? OR player_exp LIKE ? OR player_nation LIKE ? OR player_date LIKE ? OR player_num LIKE ? OR player_draft LIKE ? OR player_before LIKE ? OR type_name LIKE ? LIMIT ?,?";
		return jt.query(sql, new BeanPropertyRowMapper<PlayerInherit>(PlayerInherit.class),"%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%",(pageNum-1)*pageSize,pageSize);
	}

	@Override
	public int selectPlayerTotal(String keyword) {
		String sql = "SELECT count(1) FROM ptt WHERE player_name LIKE ? OR english_name LIKE ? OR player_titl LIKE ? OR player_body LIKE ? OR team_name LIKE ? OR player_position LIKE ?  OR player_height LIKE ? OR player_weight LIKE ? OR player_exp LIKE ? OR player_nation LIKE ? OR player_date LIKE ? OR player_num LIKE ? OR player_draft LIKE ? OR player_before LIKE ? OR type_name LIKE ?";
		return jt.queryForObject(sql, Integer.class,"%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%","%"+keyword+"%");
	}

	@Override
	public Player selectPlayerById(int playerId) {
		String sql = "SELECT * FROM player WHERE player_id =?";
		List<Player> list = jt.query(sql, new BeanPropertyRowMapper<Player>(Player.class),playerId);
		if(list.size() == 0) {
			return null;
		}else {
			return list.get(0);
		}
	}

	@Override
	public int delectPlayerById(int playerId) {
		String sql = "DELETE FROM player WHERE player_id = ?";
		return jt.update(sql, playerId);
	}

	@Override
	public int updatePlayer(Player player) {
		String sql = "UPDATE player SET player_name = ? , english_name = ? , player_titl = ? , player_body = ? , team_id = ? , player_position = ?  , player_height = ? , player_weight = ? , player_exp = ? , player_nation = ? , player_date = ? , player_num = ? , player_draft = ? , player_before = ? , type_id = ? WHERE player_id = ?";
		return jt.update(sql, player.getPlayerName(),player.getEnglishName(),player.getPlayerTitl(),player.getPlayerBody(),player.getTeamId(),player.getPlayerPosition(),player.getPlayerHeight(),player.getPlayerWeight(),player.getPlayerExp(),player.getPlayerNation(),player.getPlayerDate(),player.getPlayerNum(),player.getPlayerDraft(),player.getPlayerBefore(),player.getTypeId(),player.getPlayerId());
	}

	@Override
	public int insertPlayer(Player player) {
		String sql = "INSERT INTO player (player_name,english_name,player_titl,player_body,team_id,player_position,player_height,player_weight,player_exp,player_nation,player_date,player_num,player_draft,player_before,type_id) Value (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
		return jt.update(sql, player.getPlayerName(),player.getEnglishName(),player.getPlayerTitl(),player.getPlayerBody(),player.getTeamId(),player.getPlayerPosition(),player.getPlayerHeight(),player.getPlayerWeight(),player.getPlayerExp(),player.getPlayerNation(),player.getPlayerDate(),player.getPlayerNum(),player.getPlayerDraft(),player.getPlayerBefore(),player.getTypeId());
	}

	@Override
	public Player selectPlayerByName(String playerName) {
		String sql = "SELECT * FROM player WHERE player_name = ?";
		List<Player> list = jt.query(sql, new BeanPropertyRowMapper<Player>(Player.class), playerName);
		if(list.size() == 0) {
			return null;
		}else {
			return list.get(0);
		}
	}

}
